package com.gfwy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gfwy.dao.IPeriodDao;
import com.gfwy.vo.Period;

public class PeriodDaoImpl implements IPeriodDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public PeriodDaoImpl(Connection conn) {
		this.conn = conn;
	}
	@Override
	public boolean doCreate(Period period) throws Exception {
		boolean flag = false;
		try {
			String sql = "INSERT INTO period(pname,prelude,imgdir,title,author,pubdate) VALUES(?,?,?,?,?,?)";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, period.getPname());
			this.pstmt.setString(2, period.getPrelude());
			this.pstmt.setString(3, period.getImgdir());
			this.pstmt.setString(4, period.getTitle());
			this.pstmt.setString(5, period.getAuthor());
			this.pstmt.setTimestamp(6, new java.sql.Timestamp(period.getPubdate().getTime()));
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doUpdate(Period period) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE period SET pname=?,prelude=?,imgdir=?,title=?,author=? WHERE pid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, period.getPname());
			this.pstmt.setString(2, period.getPrelude());
			this.pstmt.setString(3, period.getImgdir());
			this.pstmt.setString(4, period.getTitle());
			this.pstmt.setString(5, period.getAuthor());
			this.pstmt.setInt(6, period.getPid());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int pid) throws Exception {
		boolean flag = false;
		try {
			String sql = "DELETE FROM period WHERE pid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, pid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public int count(String keyWords) throws Exception {
		int count = 0;
		keyWords = (keyWords == null) ? "" : keyWords;
		try {
			String sql = "SELECT COUNT(*) AS count FROM period WHERE pname LIKE ?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWords + "%");
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return count;
	}

	@Override
	public List<Period> findAll(String keyWords, int startIndex, int count)
			throws Exception {
		List<Period> list = null;
		keyWords = (keyWords == null) ? "" : keyWords;
		try {
			String sql = "SELECT * FROM period WHERE pname LIKE ? ORDER BY pname DESC LIMIT ?,?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWords + "%");
			this.pstmt.setInt(2, startIndex);
			this.pstmt.setInt(3, count);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Period>();
			while(this.rs.next()) {
				Period period = new Period();
				period.setPid(this.rs.getInt("pid"));
				period.setPname(this.rs.getString("pname"));
				period.setPrelude(this.rs.getString("prelude"));
				period.setImgdir(this.rs.getString("imgdir"));
				period.setOrd(this.rs.getInt("ord"));
				period.setTitle(this.rs.getString("title"));
				period.setAuthor(this.rs.getString("author"));
				period.setPubdate(this.rs.getDate("pubdate"));
				list.add(period);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}

	@Override
	public Period findById(int pid) throws Exception {
		Period period = null;
		try {
			String sql = "SELECT * FROM period WHERE pid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, pid);
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				period = new Period();
				period.setPid(this.rs.getInt("pid"));
				period.setPname(this.rs.getString("pname"));
				period.setPrelude(this.rs.getString("prelude"));
				period.setImgdir(this.rs.getString("imgdir"));
				period.setTitle(this.rs.getString("title"));
				period.setAuthor(this.rs.getString("author"));
				period.setPubdate(this.rs.getDate("pubdate"));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return period;
	}
	@Override
	public boolean doUpdateOrd(int ord, int pid) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE period SET ord=? WHERE pid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, ord);
			this.pstmt.setInt(2, pid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

}
